-- moves the document given by key into the folder given by FolderHierarchyKey
-- Key is either a documentVersionKey for documents, or a HierarchyKey for folders.
CREATE PROCEDURE [dbo].[asi_DocumentMove]
   @folderHierarchyKey uniqueidentifier,
   @key uniqueidentifier
AS
BEGIN
   DECLARE
      @documentVersionKey uniqueidentifier,
      @newHierarchyKey uniqueidentifier,
      @oldHierarchyKey uniqueidentifier,
      @childDocumentVersionKey uniqueidentifier,
      @documentTypeCode nchar(3)

   -- get the HierarchyKey to where it is currently linked, should be only one.  Also grab
   -- document type.  For folders, the key sent will be a hierarchy key
   SELECT TOP 1
          @oldHierarchyKey = a.HierarchyKey,
          @documentTypeCode = b.DocumentTypeCode,
          @documentVersionKey = b.DocumentVersionKey
     FROM Hierarchy a INNER JOIN DocumentMain b ON a.UniformKey = b.DocumentVersionKey
    WHERE b.DocumentVersionKey = @key
       OR a.HierarchyKey = @key
    ORDER BY b.CreatedOn DESC

   -- link the document into its new position
   exec asi_DocumentLinkDocumentOut @folderHierarchyKey, @documentVersionKey,@newHierarchyKey OUTPUT

   -- put out the new HierarchyKey so caller can get it
   SELECT @newHierarchyKey AS HierarchyKey

   -- if it is a folder, loop thru all of the children and move them as well, recursively
   IF @documentTypeCode = 'FOL'
   BEGIN
      SET ROWCOUNT 1
      SELECT @childDocumentVersionKey = UniformKey FROM Hierarchy WHERE ParentHierarchyKey = @oldHierarchyKey
      WHILE @@ROWCOUNT = 1
      BEGIN
         SET ROWCOUNT 0
         exec asi_DocumentMove @newHierarchyKey, @childDocumentVersionKey

         SET ROWCOUNT 1
         SELECT @childDocumentVersionKey = UniformKey FROM Hierarchy WHERE ParentHierarchyKey = @oldHierarchyKey
      END
      SET ROWCOUNT 0
   END

   -- remove the link to where is used to be
   DELETE FROM Hierarchy WHERE HierarchyKey = @oldHierarchyKey
END

GO